package com.example.teaching_server.repository;

import com.example.teaching_server.entity.TeachingResource;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 教学资源数据访问层
 */
@Repository
public interface TeachingResourceRepository extends JpaRepository<TeachingResource, Long> {

    /**
     * 根据子类ID和类型ID查询资源（按ID降序排列）
     */
    @Query("SELECT tr FROM TeachingResource tr WHERE tr.subcategoryId = :subcategoryId AND tr.typeId = :typeId ORDER BY tr.id DESC")
    List<TeachingResource> findBySubcategoryIdAndTypeId(@Param("subcategoryId") Long subcategoryId,
                                                        @Param("typeId") Integer typeId);

    /**
     * 根据子类ID查询所有资源（按ID降序排列）
     */
    @Query("SELECT tr FROM TeachingResource tr WHERE tr.subcategoryId = :subcategoryId ORDER BY tr.id DESC")
    List<TeachingResource> findBySubcategoryId(@Param("subcategoryId") Long subcategoryId);

    /**
     * 查询最新的教学资源（按创建时间倒序，限制4条）
     */
    List<TeachingResource> findTop4ByOrderByCreatedAtDesc();

    /**
     * 查询指定用户上传的最新教学资源（按创建时间倒序，限制2条）
     */
    List<TeachingResource> findTop2ByUploaderIdOrderByCreatedAtDesc(Integer uploaderId);

    /**
     * 查询最新的教学资源（按创建时间倒序，限制2条）
     */
    List<TeachingResource> findTop2ByOrderByCreatedAtDesc();

    /**
     * 根据关键字搜索教学资源（忽略大小写）
     */
    @Query("SELECT tr FROM TeachingResource tr WHERE " +
            "(LOWER(tr.title) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
            "LOWER(tr.description) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
            "ORDER BY tr.createdAt DESC")
    List<TeachingResource> findByKeyword(@Param("keyword") String keyword);

    /**
     * 根据子类ID和关键字搜索教学资源（忽略大小写）
     */
    @Query("SELECT tr FROM TeachingResource tr WHERE " +
            "tr.subcategoryId = :subcategoryId AND " +
            "(LOWER(tr.title) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
            "LOWER(tr.description) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
            "ORDER BY tr.createdAt DESC")
    List<TeachingResource> findBySubcategoryIdAndKeyword(@Param("subcategoryId") Long subcategoryId,
                                                         @Param("keyword") String keyword);

    /**
     * 根据子类ID、类型ID和关键字搜索教学资源（忽略大小写）
     */
    @Query("SELECT tr FROM TeachingResource tr WHERE " +
            "tr.subcategoryId = :subcategoryId AND " +
            "tr.typeId = :typeId AND " +
            "(LOWER(tr.title) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
            "LOWER(tr.description) LIKE LOWER(CONCAT('%', :keyword, '%'))) " +
            "ORDER BY tr.createdAt DESC")
    List<TeachingResource> findBySubcategoryIdAndTypeIdAndKeyword(@Param("subcategoryId") Long subcategoryId,
                                                                  @Param("typeId") Integer typeId,
                                                                  @Param("keyword") String keyword);
}
